ALTER PROC [dbo].[SP_INSERT_KHACH_THUE]
@HO_VA_TEN NVARCHAR(200), 
@NGAY_SINH DATE, 
@GIOI_TINH CHAR(1), 
@CMND VARCHAR(20), 
@NGAY_CAP DATE,
@NOI_CAP NVARCHAR(100), 
@DIA_CHI NVARCHAR(200), 
@SO_DT VARCHAR(20), 
@SO_THUE_PHONG INT,
@HINH_ANH VARCHAR(MAX), 
@LEVEL INT, 
@USR_ID INT,
@retval NVARCHAR(10) OUTPUT
AS
BEGIN
	DECLARE @ID_RETURN INT
	INSERT INTO dbo.KHACH_THUE
	(
		HO_VA_TEN, 
		NGAY_SINH, 
		GIOI_TINH, 
		CMND, 
		NGAY_CAP, 
		NOI_CAP, 
		DIA_CHI, 
		CREATE_BY, 
		UPDATE_BY, 
		CREATE_AT, 
		UPDATE_AT,		
		ACTIVE, 
		SO_DT,
		SO_THUE_PHONG, 
		LEVEL
	)
	VALUES
	(
		@HO_VA_TEN,
		@NGAY_SINH, 
		@GIOI_TINH, 
		@CMND,
		@NGAY_CAP, 
		@NOI_CAP,
		@DIA_CHI, 
		@USR_ID, 
		@USR_ID, 
		GETDATE(), 
		GETDATE(),
		'A', 
		@SO_DT,
		@SO_THUE_PHONG,
		@LEVEL
	)
	SET @ID_RETURN = (SELECT SCOPE_IDENTITY())
	UPDATE KHACH_THUE
	SET HINH_ANH = (@HINH_ANH + CONVERT(Nvarchar(10), @ID_RETURN) + '.jpg')
	WHERE
	ID = @ID_RETURN
	SET @retval = CONVERT(Nvarchar(10), @ID_RETURN);
END


CREATE PROC SP_INSERT_THU_TIEN
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		@NGAY_KT_KY_TRUOC , 
		@NGAY_KET_THUC_KY_NAY , 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'A' 
	)	
END

ALTER TABLE THUE_PHONG
ADD DON_GIA_PHONG FLOAT 

ALTER PROC [dbo].[SP_INSERT_THUE_PHONG]
@MA_PHONG INT, 
@NGAY_THUE DATE, 
@TIEN_COC FLOAT, 
@DON_GIA_PHONG FLOAT,
@USER_ID INT,
@retval NVARCHAR(10) OUTPUT
AS
BEGIN
	DECLARE @ID_RETURN INT
	INSERT INTO 
	dbo.THUE_PHONG
	(
		MA_PHONG,
		NGAY_THUE,
		TIEN_COC,
		DON_GIA_PHONG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@MA_PHONG,
		@NGAY_THUE,
		@TIEN_COC,
		@DON_GIA_PHONG,
		@USER_ID,
		@USER_ID,
		GETDATE(), 
		GETDATE(),
		'A'		
	);
	SET @ID_RETURN = (SELECT SCOPE_IDENTITY());
	SET @retval = CONVERT(Nvarchar(10), @ID_RETURN);
END

ALTER PROC [dbo].[SP_INSERT_KHACH_THUE]
@HO_VA_TEN NVARCHAR(200), 
@NGAY_SINH DATE, 
@GIOI_TINH CHAR(1), 
@CMND VARCHAR(20), 
@NGAY_CAP DATE,
@NOI_CAP NVARCHAR(100), 
@DIA_CHI NVARCHAR(200), 
@SO_DT VARCHAR(20), 
@SO_THUE_PHONG INT,
@HINH_ANH VARCHAR(MAX), 
@LEVEL INT, 
@USR_ID INT,
@retval NVARCHAR(10) OUTPUT
AS
BEGIN
	DECLARE @ID_RETURN INT
	INSERT INTO dbo.KHACH_THUE
	(
		HO_VA_TEN, 
		NGAY_SINH, 
		GIOI_TINH, 
		CMND, 
		NGAY_CAP, 
		NOI_CAP, 
		DIA_CHI, 
		CREATE_BY, 
		UPDATE_BY, 
		CREATE_AT, 
		UPDATE_AT,		
		ACTIVE, 
		SO_DT,
		SO_THUE_PHONG, 
		LEVEL
	)
	VALUES
	(
		@HO_VA_TEN,
		@NGAY_SINH, 
		@GIOI_TINH, 
		@CMND,
		@NGAY_CAP, 
		@NOI_CAP,
		@DIA_CHI, 
		@USR_ID, 
		@USR_ID, 
		GETDATE(), 
		GETDATE(),
		'A', 
		@SO_DT,
		@SO_THUE_PHONG,
		@LEVEL
	)
	SET @ID_RETURN = (SELECT SCOPE_IDENTITY())
	UPDATE KHACH_THUE
	SET HINH_ANH = (@HINH_ANH + CONVERT(Nvarchar(10), @ID_RETURN) + '.jpg')
	WHERE
	ID = @ID_RETURN
	SET @retval = CONVERT(Nvarchar(10), @ID_RETURN);
END


CREATE PROC SP_GET_MOTOS_BY_SO_THUE_PHONG
@ID INT
AS
BEGIN
	SELECT 
	ID,
	SO_XE,
	SO_KHUNG,
	SO_MAY, 
	TEN_CHU_SO_HUU,
	SO_THUE_PHONG
	FROM
	MOTO
	WHERE
	ACTIVE = 'A'
	AND SO_THUE_PHONG = @ID
END

CREATE PROC SP_GET_MOTO_INFOR_BY_ID
@ID INT
AS
BEGIN
	SELECT 
	ID,
	SO_XE,
	SO_KHUNG,
	SO_MAY, 
	TEN_CHU_SO_HUU,
	SO_THUE_PHONG
	FROM MOTO
	WHERE
	ACTIVE = 'A'
	AND ID = @ID
END

CREATE PROC SP_UPDATE_MOTO
@ID INT, 
@CHU_SO_HUU NVARCHAR(100), 
@SO_XE VARCHAR(20), 
@SO_KHUNG  VARCHAR(20),
@SO_MAY  VARCHAR(20)
AS
BEGIN
	UPDATE MOTO
	SET 
	TEN_CHU_SO_HUU = @CHU_SO_HUU, 
	SO_XE = @SO_XE, 
	SO_MAY = @SO_MAY, 
	SO_KHUNG = @SO_KHUNG
	WHERE
	ID = @ID
END

CREATE PROC SP_DELETE_MOTO
@ID INT
AS
BEGIN
	UPDATE MOTO SET ACTIVE = 'I'
	WHERE ID = @ID
END




ALTER FUNCTION F_GET_CHI_SO_NUOC
(
	@ID INT
)
RETURNS FLOAT
AS
BEGIN
	RETURN (SELECT ISNULL(MAX(CHI_SO_NUOC_SAU),0) 
	FROM dbo.THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END

ALTER FUNCTION F_GET_CHI_SO_DIEN
(
	@ID INT
)
RETURNS FLOAT
AS
BEGIN
	RETURN (SELECT ISNULL(MAX(CHI_SO_DIEN_SAU),0) 
	FROM dbo.THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END

CREATE PROC SP_GET_INFOR_BY_THUE_PHONG
@ID INT
AS
BEGIN
	SELECT 
		THUE_PHONG.MA_PHONG,
		PHONG.SO_PHONG,
		THUE_PHONG.ID, 
		KHACH_THUE.HO_VA_TEN,
		DBO.F_GET_CHI_SO_NUOC(THUE_PHONG.ID) CHI_SO_NUOC, 
		DBO.F_GET_CHI_SO_DIEN(THUE_PHONG.ID) CHI_SO_DIEN
	FROM
	THUE_PHONG,
	KHACH_THUE, 
	PHONG
	WHERE
	THUE_PHONG.ACTIVE = 'A'
	AND KHACH_THUE.ACTIVE = 'A'
	AND KHACH_THUE.LEVEL = 1
	AND THUE_PHONG.ID = @ID
	AND THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG
	AND THUE_PHONG.MA_PHONG = PHONG.ID	
END

EXEC SP_GET_INFOR_BY_THUE_PHONG 1
